package cn.getech.data.metadata.db;

import cn.getech.data.intelligence.common.exception.RRException;
import cn.getech.data.metadata.entity.meta.CollectionTableField;
import cn.getech.data.metadata.entity.meta.CollectionTableInfo;
import cn.getech.data.metadata.entity.meta.CollectionTask;
import cn.hutool.core.collection.CollectionUtil;
import org.apache.commons.lang.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SqlServerStructure extends DBStructure {

    private String clomnSqlback="SELECT COLUMN_NAME,DATA_TYPE  AS TYPE_NAME,CHARACTER_MAXIMUM_LENGTH as COLUMN_SIZE  " +
            ",(case when  IS_NULLABLE='YES' then 'Y' else 'N' end) as IS_NULLABLE, '' as REMARKS  " +
            "FROM %s.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME ='%s' and TABLE_CATALOG ='%s'  ";

    public SqlServerStructure(String url, String username, String password, String driverClass, String dbName, CollectionTask collectionTask) {
        super(url, username, password, driverClass, dbName, collectionTask);
        rowAndLengthSql = "SELECT b.rows AS tableRows,(8 * b.dpages) AS dataLength " +
                "FROM %s.sys.sysobjects AS a INNER JOIN %s.sys.sysindexes AS b ON a.id = b.id " +
                "WHERE b.indid IN (0,1) AND a.type = 'u' AND a.name='%s'";
        columnSql="SELECT \n" +
                "a.name as COLUMN_NAME,\n" +
                "b.name as TYPE_NAME,\n" +
                "COLUMNPROPERTY(a.id,a.name,'PRECISION') as COLUMN_SIZE,\n" +
                " (case when a.isnullable=1 then 'Y'else 'N' end) as IS_NULLABLE,\n" +
                "isnull(g.[value],'') AS REMARKS    \n" +
                "FROM  syscolumns a \n" +
                " left join systypes b on a.xtype=b.xusertype\n" +
                " left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id  \n" +
                "where a.id in ( \n" +
                " SELECT object_id(TABLE_CATALOG+'.'+TABLE_SCHEMA +'.'+TABLE_NAME) " +
                " FROM INFORMATION_SCHEMA.TABLES t WHERE TABLE_NAME ='%s' and TABLE_CATALOG ='%s')\n" +
                "order by a.id,a.colorder";
    }

    @Override
    protected CollectionTableInfo getRowsAndLength(CollectionTableInfo tableInfo) {
        Connection conn = null;
        try {
            if (StringUtils.isEmpty(rowAndLengthSql)) {
                return null;
            }
            String sql =String.format(rowAndLengthSql,tableInfo.getDbName(),
                    tableInfo.getDbName(),tableInfo.getTableName());
            conn = getConnection();
            if (conn == null)
               return null;
            Statement sta = conn.createStatement();
            ResultSet result = sta.executeQuery(sql);
            while (result != null&& result.next()) {
                CollectionTableInfo returnTable = new CollectionTableInfo();
                returnTable.setRowsNum(result.getLong("tableRows"));
                returnTable.setTotalSize(result.getDouble("dataLength"));
                return returnTable;
            }
        } catch (Exception e) {
            log.warn("获取表大小错误:{}",e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
        }
        return null;
    }

    @Override
    protected List<CollectionTableField> getSourceColumns(CollectionTableInfo tableInfo) {
        List<CollectionTableField> list = new ArrayList<>();
        Connection conn = null;
        try {
            conn = getConnection();
            if (conn == null)
                throw new RRException("获取数据库连接失败");
            ResultSet result;
            String sql = String.format(columnSql,tableInfo.getTableName(),tableInfo.getDbName());
            Statement st = conn.createStatement();
            result = st.executeQuery(sql);
            if (result.getRow()==0) {
                result = st.executeQuery(String.format(clomnSqlback,tableInfo.getDbName(),
                        tableInfo.getTableName(),tableInfo.getDbName()));
            }
            while (result != null && result.next()) {
                String columnName = result.getString("COLUMN_NAME");
                String typeName = result.getString("TYPE_NAME");
                CollectionTableField tableField = new CollectionTableField();
                tableField.setFieldType(typeName);
                tableField.setFieldName(columnName);
                tableField.setFieldDesc(result.getString("REMARKS"));
                //外部采集的数据，肯定没有分区字段
                tableField.setIsPartition(0);
                //设置字段长度
                Integer columnSize = result.getString("COLUMN_SIZE") == null ? null :
                        Integer.parseInt(result.getString("COLUMN_SIZE"));
                tableField.setFieldLength(columnSize);
                //是否为空字段
                Integer isNullabelInt = null;
                isNullabelInt = result.getString("IS_NULLABLE") == null ||
                        "N".equalsIgnoreCase(result.getString("IS_NULLABLE")) ? 0 : 1;
                //设置是否为空
                tableField.setFieldIsNull(isNullabelInt);
                List<String> primaryKeyNameList = getPrimayKeyName(conn, tableInfo.getTableName(), this.driverClass);
                //设置是否主键
                if (CollectionUtil.isNotEmpty(primaryKeyNameList) && primaryKeyNameList.contains(columnName)) {
                    tableField.setPrimaryKey(1);
                } else {
                    tableField.setPrimaryKey(0);
                }
                list.add(tableField);
            }
        } catch (Exception e) {
            log.error("", e);
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error("", e);
            }
        }
        //设置默认数据密级

        return getCollectionTableFields(tableInfo, list);
    }
}
